﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;
using System.Collections.Generic;
using NPOI.HSSF.UserModel;
using System;
using Business.WarehouseLocationManagement.Dto;

public class Helper
{
    public static List<WarehouseLocationDto> ImportExcelData(string filePath)
    {
        List<WarehouseLocationDto> locations = new List<WarehouseLocationDto>();
        try
        {
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                // 根据文件后缀判断是XLS还是XLSX格式，创建对应的工作簿对象
                IWorkbook workbook;
                if (Path.GetExtension(filePath).ToLower() == ".xlsx")
                {
                    workbook = new XSSFWorkbook(file);
                }
                else
                {
                    workbook = new HSSFWorkbook(file);
                }

                // 获取第一个工作表（可以根据实际需求修改获取工作表的逻辑）
                ISheet sheet = workbook.GetSheetAt(0);

                // 遍历行（从第二行开始，假设第一行是标题行）
                for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                {
                    IRow row = sheet.GetRow(rowIndex);
                    if (row != null)
                    {
                        WarehouseLocationDto locationDto = new WarehouseLocationDto();

                        ICell PCode = row.GetCell(0);
                        if (PCode != null)
                        {
                            locationDto.PCode = PCode.StringCellValue;
                        }

                        ICell Code = row.GetCell(1);
                        if (Code != null)
                        {
                            locationDto.Code = Code.StringCellValue;
                        }
                        ICell LocationType = row.GetCell(2);
                        if (LocationType != null)
                        {
                            locationDto.LocationType = LocationType.StringCellValue;
                        }
                        var UsePattern = row.GetCell(3);
                        if (UsePattern != null)
                        {
                            locationDto.UsePattern = UsePattern.StringCellValue;
                        }
                        ICell LocationLength = row.GetCell(4);
                        if (LocationLength != null)
                        {
                            locationDto.LocationLength = Convert.ToInt64(LocationLength.NumericCellValue);
                        }
                        ICell LocationWidth = row.GetCell(5);
                        if (LocationWidth != null)
                        {
                            locationDto.LocationWidth = Convert.ToInt64(LocationWidth.NumericCellValue);
                        }
                        ICell LocationHeight = row.GetCell(6);
                        if (LocationHeight != null)
                        {
                            locationDto.LocationHeight = Convert.ToInt64(LocationHeight.NumericCellValue);
                        }
                        ICell LocationVolume = row.GetCell(7);
                        if (LocationVolume != null)
                        {
                            locationDto.LocationVolume = Convert.ToInt64(LocationVolume.NumericCellValue);
                        }
                        ICell LoadBearing = row.GetCell(8);
                        if (LoadBearing != null)
                        {
                            locationDto.LoadBearing = Convert.ToInt64(LoadBearing.NumericCellValue);
                        }
                        ICell LockPriority = row.GetCell(9);
                        if (LockPriority != null)
                        {
                            locationDto.LockPriority = LockPriority.StringCellValue;
                        }
                        ICell PriorityGoods = row.GetCell(10);
                        if (PriorityGoods != null)
                        {
                            locationDto.PriorityGoods = PriorityGoods.StringCellValue;
                        }
                        locations.Add(locationDto);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            // 这里可以记录日志，比如使用日志框架记录详细的异常信息
            Console.WriteLine($"导入Excel文件出现异常: {ex.Message}");
        }
        return locations;
    }
}